﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using WebApiMIT3.Database;
using WebApiMIT3.Models;

namespace WebApiMIT3.DatabaseAccessLayer
{
    public class CategoryMN
    {
        private const string TableName = "CategoryMN";
        private const string CategoryID = "CategoryID";
        private const string InfoID = "InfoID";

        private Category category = new Category();
        private PoliticalInfo politicalInfo = new PoliticalInfo();

        public void InsertStatement(DataModel dataModel)
        {
            foreach (var categoryValue in dataModel.Categories)
            {
                this.CheckIfCategoryExists(dataModel, categoryValue);

                var politicalInfo = new PoliticalInfo();
                var politicalInfoID = politicalInfo.SelectIndex(dataModel.SourceUrl);

                var categoryID = this.category.SelectIndex(categoryValue);

                var builder = new StringBuilder();

                builder.Append(string.Format("INSERT INTO {0} ", TableName));
                builder.Append(string.Format("({0}, {1}) ", InfoID, CategoryID));
                builder.Append(string.Format("VALUES ('{0}', '{1}');", politicalInfoID, categoryID));

                DatabaseConnect.Insert(builder.ToString());
            }
        }

        public List<string> SelectCategoryIndexes(DataModel dataModel)
        {
            var result = new List<string>();

            var builder = new StringBuilder();

            var politicalInfo = new PoliticalInfo();
            var politicalInfoID = politicalInfo.SelectIndex(dataModel.SourceUrl);

            builder.Append(string.Format("SELECT {0} ", CategoryID));
            builder.Append(string.Format("FROM {0} ", TableName));
            builder.Append(string.Format("WHERE {0}='{1}';", InfoID, politicalInfoID));

            return new List<string>();
        }

        public List<DataModel> SelectCategories(List<DataModel> dataModelList)
        {
            List<string> infoIDs = new List<string>();
            if (dataModelList != null && !dataModelList.Any())
            {
                return new List<DataModel>();
            }

            for (int i = 0; i < dataModelList.Count; i++)
            {
                infoIDs.Add(dataModelList[i].InfoID);
            }

            var result = new List<DataModel>();
            var builder = new StringBuilder();

            builder.Append(string.Format("SELECT DISTINCT a.{0},  b.{1} ", InfoID, this.category.CategoryName));
            builder.Append(string.Format("FROM {0} a ", TableName));
            builder.Append(string.Format("INNER JOIN {0} b ", this.category.TableName));
            builder.Append(string.Format("ON a.{0}=b.{1} ", CategoryID, this.category.CategoryID));
            builder.Append(string.Format("WHERE a.{0}='{1}' ", InfoID, infoIDs[0]));

            if (infoIDs.Count > 1)
            {
                for (int i = 1; i < infoIDs.Count; i++)
                {
                    builder.Append(string.Format("OR a.{0}='{1}'", InfoID, infoIDs[i]));
                }
            }

            Console.WriteLine(builder.ToString());

            result = this.SetModels(dataModelList, builder);

            return result != null && result.Any()
                ? result
                : dataModelList;
        }

        public int CountStatement()
        {
            var builder = new StringBuilder();

            builder.Append(string.Format("SELECT * FROM {0} ", TableName));

            return DatabaseConnect.Count(builder.ToString());
        }

        public void DeleteAllStatement()
        {
            var builder = new StringBuilder();

            builder.Append(string.Format("DELETE FROM {0} ", TableName));

            DatabaseConnect.Delete(builder.ToString());
        }

        public void DeleteAllStatementBasedOnID(string infoIDValue)
        {
            var builder = new StringBuilder();

            builder.Append(string.Format("DELETE FROM {0} ", TableName));
            builder.Append(string.Format("WHERE {0}= {1} ;", InfoID, infoIDValue));

            DatabaseConnect.Delete(builder.ToString());
        }

        public void UpdateStatement(DataModel dataModel, string politicalInfoID)
        {
            this.DeleteAllStatementBasedOnID(politicalInfoID);
            this.InsertStatement(dataModel);
        }

        private void CheckIfCategoryExists(DataModel dataModel, string categoryValue)
        {
            var queryValue = this.category.SelectStatement(categoryValue);
            bool doesExists = !string.IsNullOrEmpty(queryValue);

            if (!doesExists)
            {
                this.category.InsertStatement(dataModel);
            }
        }

        private string ReplaceDBChars(string value)
        {
            return value.Replace("'", "''");
        }

        private List<DataModel> SetModels(List<DataModel> dataModelList, StringBuilder builder)
        {
            var rowCount = DatabaseConnect.SelectList(builder.ToString());
            for (int i = 0; i < rowCount.Count; i++)
            {
                for (int j = 0; j < dataModelList.Count; j++)
                {
                    if (dataModelList[j].InfoID == rowCount[i][0])
                    {
                        if (dataModelList[j].Categories == null)
                        {
                            dataModelList[j].Categories = new List<string>();
                        }

                        dataModelList[j].Categories.Add(rowCount[i][1]);
                    }
                }
            }

            return dataModelList;
        }
    }
}